package edu.toronto.whimper.DataProcessor;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter
{
	//table rows
	//check how to index
	public static String KEY_ROWID = "_id";
    public static String KEY_DATE = "date";
    public static String KEY_NOISE = "noise";
    public static String KEY_LATITUDE = "latitude";
    public static String KEY_LONGITUDE = "longitude";
    public static String KEY_ALTITUDE = "altitude";
    
    private static String TAG = "DBAdapter";
    
    private static String DATABASE_NAME = "whimperDB";
    private static final String DATABASE_TABLE = "noisesamples";
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE = " create table " + DATABASE_TABLE +
    		" ("+ KEY_ROWID + " integer primary key autoincrement, " +
    		KEY_DATE +" datetime not null, " + KEY_NOISE + " double not null, " + KEY_LATITUDE + " double not null, " + KEY_LONGITUDE + " double not null, "
    		+ KEY_ALTITUDE + " double not null)";
    
    private final Context context; 
    
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    public DBAdapter(Context ctx) 
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }
        
    private static class DatabaseHelper extends SQLiteOpenHelper 
    {
        DatabaseHelper(Context context) 
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) 
        {
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, 
                              int newVersion) 
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion 
                  + " to "
                  + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS users");
            onCreate(db);
        }
    }   
    //---opens the database---
    public DBAdapter open() throws SQLException 
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    //---closes the database---    
    public void close() 
    {
        DBHelper.close();
    }
    
    //---insert a noise sample into the database---
    public long insertNoiseSample(String date, double noise, double latitude, double longitude, double altitude) 
    {
        ContentValues initialValues = new ContentValues();
        
        initialValues.put(KEY_DATE, date);
        initialValues.put(KEY_NOISE, noise);
        initialValues.put(KEY_LATITUDE, latitude);
        initialValues.put(KEY_LONGITUDE, longitude);
        initialValues.put(KEY_ALTITUDE, altitude);
        
        return db.insert(DATABASE_TABLE, null, initialValues);
    }
    //---deletes a particular noise sample by id---
    public boolean deleteNoiseSample(long rowId) 
    {
        return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }
    //---deletes all
    public boolean deleteAll() 
    {
    	int nRows = 0; 
    	db.beginTransaction(); 
    	try { 
    	  nRows = db.delete(DATABASE_TABLE, null,null); 
    	  db.setTransactionSuccessful(); // implies commit at endTransaction 
    	} 
    	catch (Exception e) {
			// TODO: handle exception
		}
        finally { 

    	  db.endTransaction(); 
    	} 

    	return nRows > 0; 
    }
    
    public boolean getCount() 
    {
    	//SELECT Count(*) FROM tblName
        //return db.sde > 0;
    	return true;
    }

    //---retrieves all Noise Samples---
    public Cursor getAllNoiseSamples() 
    {
        return db.query(DATABASE_TABLE, new String[] {
        		KEY_ROWID, 
        		KEY_DATE, KEY_NOISE, KEY_LATITUDE, KEY_LONGITUDE, KEY_ALTITUDE}, 
                null, 
                null, 
                null, 
                null, 
                null);
    }

    //---retrieves a particular Noise Sample---
    public Cursor getNoiseSample(long rowId) throws SQLException 
    {
        Cursor mCursor =
                db.query(true, DATABASE_TABLE, new String[] {
                		KEY_ROWID,
                		KEY_DATE, KEY_NOISE, KEY_LATITUDE, KEY_LONGITUDE
                		}, 
                		KEY_ROWID + "=" + rowId, 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    //---retrieves a particular noise sample---
    public Cursor getNoiseSample(double noise) throws SQLException 
    {
        Cursor mCursor =
                db.query(true, DATABASE_TABLE, new String[] {
                		KEY_ROWID,
                		KEY_NOISE
                		}, 
                		KEY_NOISE + "=" + noise, 
                		null,
                		null, 
                		null, 
                		null, 
                		null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    //---updates a Noise Sample---
    public boolean updateNoiseSample(long rowId, double noise) 
    {
        ContentValues args = new ContentValues();
        args.put(KEY_NOISE, noise);
        return db.update(DATABASE_TABLE, args, 
                         KEY_ROWID + "=" + rowId, null) > 0;
    }

}
